package com.example.springboot.excel.poi;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.SimpleDateFormat;

/**
 * <p>
 * </p>
 *
 * @author Zero
 * @since 2019/6/14
 */
public class ExcelPoiUtils {

    private static Logger logger = LoggerFactory.getLogger(ExcelPoiUtils.class);

    public static void main(String[] args) {
        File file = new File("/Users/kermit/Desktop/poi.xlsx");
        try {
            JSONObject jsonObject = convert(file);
            logger.debug("解析结果：" + jsonObject.toJSONString());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static boolean verifySuffix(String filename) {
        if (!filename.matches("^.+\\.(?i)(xls)$") && !filename.matches("^.+\\.(?i)(xlsx)$")) {
            return false;
        }
        return true;
    }

    public static boolean verifyHeader(String hearder) {
        return true;
    }

    public static JSONObject convert(MultipartFile file) throws IOException {
        return convert(file.getInputStream());
    }

    public static JSONObject convert(File file) throws IOException {
        return convert(new FileInputStream(file));
    }

    public static JSONObject convert(InputStream is) throws IOException {
        JSONObject jsonObject = new JSONObject(true);
        logger.debug("开始解析################");
        Workbook workbook = WorkbookFactory.create(is);
        int sheetCount = workbook.getNumberOfSheets();
        logger.debug("工作表数量：" + sheetCount);
        for (int i = 0; i < sheetCount; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            String sheetName = sheet.getSheetName();
            logger.debug("开始解析：" + sheetName);
            // 总行数
            int rows = sheet.getLastRowNum() + 1;
            logger.debug("共计行数：" + rows);
            // 第一行
            Row tmp = sheet.getRow(0);
            if (tmp == null) {
                logger.debug("数据为空");
                continue;
            }
            // 总列数（由第一行列头确定）
            int cols = tmp.getPhysicalNumberOfCells();
            logger.debug("共计列数：" + cols);
            String[] headers = new String[cols];
            for (int col = 0; col < cols; col++) {
                Cell cell = tmp.getCell(col);
                Object value = getObjectCellValue(cell);
                headers[col] = String.valueOf(value);
            }
            JSONArray rowArray = new JSONArray();
            for (int row = 1; row < rows; row++) {
                Row r = sheet.getRow(row);
                JSONObject rowObject = new JSONObject();
                for (int col = 0; col < cols; col++) {
                    Cell cell = r.getCell(col);
                    Object value = getObjectCellValue(cell);
                    rowObject.put(headers[col], value);
                }
                rowArray.add(rowObject);
            }
            jsonObject.put(sheetName, rowArray);
        }
        logger.debug("解析结束################");
        return jsonObject;
    }

    private static Object getObjectCellValue(Cell cell) {
        Object value = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case _NONE:
                    break;
                case NUMERIC:
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 方式1
                        //value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        // 方式2
                        value = sdf.format(cell.getDateCellValue());
                    } else {
                        value = cell.getNumericCellValue();
                    }
                    break;
                case STRING:
                    value = cell.getStringCellValue();
                    break;
                case FORMULA:
                    value = cell.getCellFormula();
                    break;
                case BLANK:
                    value = "";
                    break;
                case BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case ERROR:
                    value = "ERROR";
                    break;
                default:
                    value = "";
                    break;
            }
        }
        return value;
    }
}
